﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QueryData;

internal class 连接查询
{
    void MyMethod()
    {
        using (var db = new BasicDbContext())
        {
            // 基础连接查询
            (from x in db.BasicEntities
             join y in db.MainEntities on x.Id equals y.Id
             select new { x, y }).ToList();

            // EF Core不支持翻译分组连接，因为关系数据库不支持
            (from x in db.BasicEntities
             join y in db.MainEntities on x.Id equals y.Id into grouping
             select new { x, grouping }).ToList();

            // 基本SelectMany操作，翻译为笛卡尔连接
            (from x in db.BasicEntities
             from y in db.MainEntities
             select new { x, y }).ToList();

            // 由于这个where条件和连接条件等效，因此翻译为内连接
            (from x in db.BasicEntities
             from y in db.MainEntities.Where(m => m.Id == x.Id)
             select new { x, y }).ToList();

            // 由于调用了DefaultIfEmpty方法，翻译为左外连接
            (from x in db.BasicEntities
             from y in db.MainEntities.Where(m => m.Id == x.Id).DefaultIfEmpty()
             select new { x, y }).ToList();

            // 在第二个数据集中引用第一个数据集的Select操作会翻译为cross apply连接，具体要看数据库是否支持这种连接
            (from x in db.BasicEntities
             from y in db.MainEntities.Select(m => m.Id.ToString() + ": " + x.Text)
             select new { x, y }).ToList();

            // 调用DefaultIfEmpty的cross apply连接会翻译为outer apply连接，具体要看数据库是否支持这种连接
            (from x in db.BasicEntities
             from y in db.MainEntities.Select(m => m.Id.ToString() + ": " + x.Text).DefaultIfEmpty()
             select new { x, y }).ToList();

            // 专门模拟左外连接的查询模式，关键在于into操作后接from操作
            (from x in db.BasicEntities
             join y in db.MainEntities on x.Id equals y.Id into grouping
             from g in grouping.DefaultIfEmpty()
             select new { x, g }).ToList();

            // 多张表的连续左外连接，最少使用方法语法
            (from x in db.BasicEntities
             join y in db.MainEntities on x.Id equals y.Id into grouping
             from g in grouping.DefaultIfEmpty()
             join z in db.SubEntities on g.Id equals z.Id into grouping2
             from g2 in grouping2.DefaultIfEmpty()
             select new { x, g, g2 }).ToList();

            // 多张表的连续左外连接，看上去更简洁一些
            (from x in db.BasicEntities
             from y in db.MainEntities.Where(m => m.Id == x.Id).DefaultIfEmpty()
             from z in db.SubEntities.Where(s => s.Id == y.Id).DefaultIfEmpty()
             select new { x, y, z }).ToList();
        }
    }

}
